﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;

using Frame.Lib.Core.Base;
using Cms.Entity.Article;
using Frame.Lib.Utilities;
using Frame.Lib.Core.Attach;

namespace Cms.DAL.Article
{
    public class ArticleDAL : BaseDAL
    {
        private const string ORDER_BY = " order by IsRecommend desc,Orders desc,InputTime desc ";

        //public string GetPagerCondition(string name)
        //{
        //    string condition = null;
        //    if (name != null && name != "")
        //    {
        //        condition = " Name like @p1";
        //    }
        //    return condition;
        //}       

        //public void AddMenuName(IEnumerable<ArticleInfo> list)
        //{
        //    DataTable menu = dbOper.Query("select ID,Name from CmsMenu");
        //    DataTable menuArticle = dbOper.Query("select * from  CmsMenuArticle");
        //    foreach (ArticleInfo article in list)
        //    {
        //        article.MenuIDs = new List<int>();
        //        foreach (DataRow dr in menuArticle.Select("ArticleID = " + article.ID))
        //        {
        //            article.MenuIDs.Add((int)dr["MenuID"]);
        //        }
        //        string menuIDs = ConvertUtil.ConvertToDbStr(article.MenuIDs);

        //        foreach (DataRow dr in menu.Select("ID in (" + menuIDs + ")"))
        //        {
        //            article.MenuName += "," + dr["Name"];
        //        }
        //        article.MenuName = (article.MenuName ?? "").Trim(',');
        //    }
        //}

        public List<ArticleInfo> GetListByMenu(List<int> menuIDs,string name)
        {
            //string strMenuIDs = ConvertUtil.ConvertToDbStr(menuIDs);
            //string sql = "select * from  [CmsArticle] where  ID in (select ArticleID from  CmsMenuArticle where MenuID in (" + strMenuIDs + "))";

            string sql = null;
            if (menuIDs.Count == 1)
            {
                sql = "select * from " + CTableName.Article + " where MenuID = " + menuIDs.First();
            }
            else
            {
                sql = "select * from " + CTableName.Article + " where MenuID in (" + ConvertUtil.ConvertToDbStr(menuIDs) + ")";
            }


            if (!string.IsNullOrEmpty(name))
            {
                sql += " and Name like @p0 ";
            }

            sql += ORDER_BY;

            List<ArticleInfo> articles = dbOper.Query<ArticleInfo>(sql, "%" + name + "%");
            this.SetPictureCount(articles);

            return articles;
        }

        public void SetPictureCount(List<ArticleInfo> articles)
        {
            if (articles.Count > 0)
            {
                string id = ConvertUtil.ConvertToDbStr(articles.Select(t => t.ID));
                string sql = "select ID,(select count(*) from  "+ CTableName.ArticlePicture + " where BindID = " + CTableName.Article + ".ID) as PictureCount from  " + CTableName.Article;
                List<ArticleInfo> tmp = dbOper.Query<ArticleInfo>(sql);

                foreach (ArticleInfo info in articles)
                {
                    IEnumerable<ArticleInfo> srcs = tmp.Where(t => t.ID == info.ID);
                    if (srcs.Count() > 0)
                    {
                        info.PictureCount= srcs.First().PictureCount;
                    }
                }
            }
        }

        public List<ArticleInfo> GetDisplayList(List<int> menuIDs, int? recordCount)
        {
            string strMenuIDs = ConvertUtil.ConvertToDbStr(menuIDs);
            string sql = null;
            if (recordCount == null)
            {
                sql = "select * from  "+ CTableName.Article + " where  MenuID in (" + strMenuIDs + ") and IsDisplay=1 " + ORDER_BY;
            }
            else
            {
                sql = "select top " + recordCount + " * from  " + CTableName.Article + " where  MenuID in (" + strMenuIDs + ") and IsDisplay=1" + ORDER_BY;
            }
            List<ArticleInfo> articles = dbOper.Query<ArticleInfo>(sql);
            
            return articles;
        }

        public int? GetFirstID(int menuID)
        {
            string sql = "select top 1 ID from  " + CTableName.Article + " where  MenuID = @p0 " + ORDER_BY;
            List<int> articles = dbOper.Query<int>(sql, menuID);
            if (articles.Count == 0)
            {
                return null;
            }
            else
            {
                return articles.First();
            }
        }

        public void MoveMenu(List<int> articleIDs, int menuID)
        {
            string sql = "upate " + CTableName.Article + " set MenuID = @p0 where ArticleID in (" + ConvertUtil.ConvertToDbStr(articleIDs) + ")";

            dbOper.RunSql(sql, menuID);
        }

        //public void SaveMenu(ArticleInfo info)
        //{
        //    string sql = "delete from CmsMenuArticle where ArticleID=@p0";
        //    dbOper.RunSql(sql, info.ID);
        //    for (int i = 0; i < info.MenuIDs.Count; i++)
        //    {
        //        sql = "insert into CmsMenuArticle(MenuID,ArticleID) values( @p0, @p1 )";
        //        dbOper.RunSql(sql, info.MenuIDs[i], info.ID);
        //    }
        //}

        //public static List<int> GetMenuIDs(int articleID)
        //{
        //    string sql = "select MenuID from  CmsMenuArticle where ArticleID = @p0";
        //    List<int> menuIDs = DBHelper.Instance().Query<int>(sql, articleID);

        //    return menuIDs;
        //}

        /// <summary>
        /// 获取每个文章的第一个附件
        /// </summary>
        /// <param name="menuID"></param>
        /// <returns></returns>
        public List<AttachInfo> GetAttachList(int menuID)
        {
            string sql = "select * from " + CTableName.ArticleAttach + " where id in " +
                         "(select min(a.ID) from " + CTableName.ArticleAttach + " as a inner join " + CTableName.Article + " as b on a.BindID= b.id " +
                          "where b.MenuID  = @p0 " + 
                          "Group By a.BindID)";
            List<AttachInfo> list = dbOper.Query<AttachInfo>(sql, menuID);
            return list;
        }

    
        public List<int> GetPictureIDs(object id)
        {
            string sql = "select ID from  " + CTableName.ArticlePicture + " where BindID = @p0";
            List<int> pictureIDs = dbOper.Query<int>(sql, id);
            return pictureIDs;
        }

        public object GetPictureCount(int articleID)
        {
            throw new NotImplementedException();
        }

        public List<int> GetIDsByAttachIDs(List<int> attachIDs)
        {
            string attachids = ConvertUtil.ConvertToDbStr(attachIDs);
            string sql = "select distinct BindID from " + CTableName.ArticleAttach + " where ID in (" + attachids + ")";

            List<int> ids = dbOper.Query<int>(sql);
            return ids;
        }



        public IEnumerable<int> GetIDsByMenuIDs(IEnumerable<int> menuIDs)
        {
            string sql = "select ID from " + CTableName.Article + " where MenuID in (" + ConvertUtil.ConvertToDbStr(menuIDs) + ")";
            List<int> ids = dbOper.Query<int>(sql);
            return ids;
        }
    }

}
